TAD's Quick ASP/Database tut 3
Databases
A database (in the most basic form) is a collection of data structures called 'records'. These data structures are grouped into 'tables' so, for example, all the user-information records (first name, last name, user-name, password, email etc...) would be stored in the "UsersTable" and messages records could be stored in a table called "MsgTable".
You define these 'records' in your database program (such as Microsoft Access) using the table design-view. You can think of records as structures and tables as a list of structures. Using a SQL (Structure Query Language) statement you can search these records, insert new ones, delete or update existing records.
Creating the Database
First things first, fire up Monkeyslap Office (or your database app) and create a blank database. Save it into your c:\inetpub\wwwroot\mysite folder with the name "mydb.mdb".
Now let's design the record structures for our database. One of the key principles behind database design is to reduce duplicated data to a bare minimum - if you are storing the same data more than once then you're probably doing something wrong. For example, instead of storing the UserName and Email with each and every message we only need to store the ID of the author. Using this ID number we can then search the UsersTable and pull out the correct UserName, Email and anything else we want :)
Here is the 'UsersTable' record structure. As you can see it's pretty straight forward to understand. We will soon write the registration and 'profile edit' ASP pages for it.
Field name DataType Description
ID ** AutoNumber record ID (primary key)
firstName Text[50] First name of user
lastName Text[50] last name of user
email Text[100] email
userName Text[50] onscreen username
password Text[50] password
The ID is a number of the record within a database table. So knowing just the table name and the ID we can go directly to a particular record. The AutoNumber attribute means that the database automatically assigns a unique ID whenever we insert a NEW record. The ID is also the 'primary key' for the table - this means this field in the table IS (and MUST be) unique. Most of the time you will be using the ID=AutoNumber & Primary key technique.
Close the table design window and you will be prompted to save the table, choose Yes and enter "UsersTable" as a name.
Next we will create the 'MsgTable' record structure. This one will contain the actual messages together with the date they were created and the userID of who wrote them. We will also store a parentMsgID which can be used later to reply to a particular message.
Field name DataType Description
ID ** AutoNumber record ID (primary key)
created Date/Time When this was created
userID Number ID of the author
parentMsgID Number ID of the parent msg
message Memo the actual msg/flame :)
This time close and save the table as "MsgTable".
Setting the DSN
We have defined the empty record structures for the database. Before we can start inserting stuff into the database using some ASP code we need to make a connection to the database. This is done using a Connection object. There are two ways to tell it where the actual 'mydb.mdb' file is on the server; either using a DSN (Data Source Name) or by using a DSN-less (basically using "c:\inetpub\wwwroot\mysite\mydb.mdb"). I strongly suggest using a DSN to make life easier because when you're developing a site you often need to move files around and having to change the path of a file on every ASP page would be very boring ;)
Go to the Start-Menu -> Control Panel -> Admin tools -> Data sources (ODBC) program and choose the System tab and hit Add then choose your database driver (eg. Microsoft Access Driver (*.mdb) ) Enter the Data Source Name as 'mydb' and Select the file c:\inetpub\wwwroot\mysite\mydb.mdb.
If you encounter any errors (such as "directory/path not found" or similiar messages) then try uninstalling your firewall and/or anti-virus program. Sorry, but I can't answer any questions so use google or your favourite search engine/newsgroup for more information.
Testing the DSN
To check if everything is okay use the following code. It opens a database connection using our new DSN ('mydb') and attempts to display the userName from the 1st record from the UsersTable.
File - testdsn.asp
<%@ Language=VBScript %>
<% Option Explicit %>
<%
'// create a database connection //
Dim Conn
Set Conn = Server.CreateObject("ADODB.Connection")
Conn.Open "DSN=myDB"
%>
<html>
<head>
<title>Test DSN connection</title>
</head>
<%
'// query the Database //
Dim RS
Set RS = Conn.Execute("SELECT * FROM UsersTable")
'// display the 'userName' field from our recordset
If Not RS.EOF Then
Response.Write "username:" & RS("userName")
End If
%>
</html>
Records, SQL & Recordsets
In order to pass data between our ASP code and our database records we use RecordSets. Most of the time we will be serving up data FROM the database. In order to tell the database what data records we want we use SQL (Structure Query Language) statements. The results from the database are stored in a RecordSet (a small object containing the data from a SINGLE database record). Using this RecordSet object we can move through each record and display the fields we want.
But before we can display anything we first need to place some data into the database, we can do that with the following page.
NOTE: There is NO validation and NO error checking on this page (this is something we can add later).
Example - testusers.asp
<%@ Language=VBScript %>
<% Option Explicit %>
<%
'// create a database connection //
Dim Conn
Set Conn = Server.CreateObject("ADODB.Connection")
Conn.Open "DSN=myDB"
Const adOpenStatic = 3
Const adLockPessimistic = 2
'// perform different actions depending on which form
'// was submitted - QueryString tells us which :)
Dim action, RS, userID
action = Request.QueryString("action")
Select Case action
Case "create"
Set RS = Server.CreateObject ("ADODB.Recordset")
RS.Open "SELECT * FROM UsersTable", Conn, adOpenStatic, adLockPessimistic
RS.AddNew
RS("firstName") = Request.Form("firstname")
RS("lastName") = Request.Form("lastName")
RS("email") = Request.Form("email")
RS("userName") = Request.Form("username")
RS("password") = Request.Form("password")
RS.Update
Case "delete"
userID = Request.Form("ID")
RS = Conn.Execute("DELETE * FROM UsersTable WHERE ID="&userID)
End Select
%>
<html>
<head>
<title>test UsersTable :)</title>
</head>
<body>
<table width="100%" border="0" cellspacing="2" cellpadding="0">
<tr>
<td width="16%" align="center">ID</td>
<td width="16%" align="center">firstName</td>
<td width="16%" align="center">lastName</td>
<td width="16%" align="center">email</td>
<td width="16%" align="center">userName</td>
<td width="16%" align="center">password</td>
</tr>
<%
Set RS = Conn.Execute("SELECT * FROM UsersTable")
If RS.EOF Then
%>
<tr>
<td colspan="6" align="center">Found 0 users</td>
</tr>
<% Else
While Not RS.EOF
%>
<tr>
<td align="center"><%=RS("ID")%></td>
<td align="center"><%=RS("firstName")%></td>
<td align="center"><%=RS("lastName")%></td>
<td align="center"><%=RS("email")%></td>
<td align="center"><%=RS("userName")%></td>
<td align="center"><%=RS("password")%></td>
</tr>
<%
RS.MoveNext
Wend
End If
%>
</table>
<hr>
<form name="form1" method="post" action="testusers.asp?action=create">
<h1>Create new User</h1>
<p>
firstName:
<input name="firstname" type="text" id="firstname" maxlength="50">
lastname:
<input name="lastname" type="text" id="lastname" maxlength="50">
<br>
email:
<input name="email" type="text" id="email" size="64" maxlength="100">
<br>
userName:
<input name="username" type="text" id="username" maxlength="50">
password:
<input name="password" type="text" id="password" maxlength="50">
<br>
<input type="submit" name="Submit" value="Create user">
</p>
</form>
<hr>
<form name="form1" method="post" action="testusers.asp?action=delete">
<h1>Delete User</h1>
<p> ID:
<input name="id" type="text" id="id" maxlength="10">
<br>
<br>
<input type="submit" name="Submit2" value="Delete User">
</p>
</form>
<br>
</body></html>
The above code performs three tasks. 1) It displays all the users in the UsersTable of our database. 2) It allows new users to be created. 3) You can delete users by entering their unique ID number. As you can see most of the listing is taken up by plain old HTML code with only a small part being actual ASP code.
Summary
Believe it or not, we have covered most of the fundamentals about ASP and Database programming :)
Next time we will look at the different SQL query formats and how to perform simple searches through our database.